﻿using housingsafety.server.Models;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace housingsafety.server.Helpers
{
    public class Utilitys
    {
        public static string GetLayerNewName(string name, List<string> names)
        {
            var n = 0;
            var newName = name;
            if (names != null && names.Count > 0)
            {
                while (true)
                {
                    if (!names.Contains(newName))
                    {
                        break;
                    }

                    newName = $"{name}_{++n}";
                }
            }

            return newName;
        }

        public static string FldType2Str(sbyte? FldType)
        {
            var fldTypeStr = "varchar(255)";
            if (FldType.HasValue)
            {
                if (FldType.Value == 0)
                {
                    fldTypeStr = "varchar(255)";
                }
                else if (FldType.Value == 1)
                {
                    fldTypeStr = "double precision";
                }
                else if (FldType.Value == 2)
                {
                    fldTypeStr = "int8";
                }
                else if (FldType.Value == 3)
                {
                    fldTypeStr = "timestamp(6)";
                }
                else if (FldType.Value == 4)
                {
                    fldTypeStr = "varchar(50)";
                }
                else if (FldType.Value == 5)
                {
                    fldTypeStr = "int2";
                }
            }

            return fldTypeStr;
        }

        public static string GenerateLayId()
        {
            return $"a{SequentialGuid.SequentialSqlGuidGenerator.Instance.NewGuid().ToString("N")}";
        }

        public static string GenerateTeamLayId()
        {
            return $"t{SequentialGuid.SequentialSqlGuidGenerator.Instance.NewGuid().ToString("N")}";
        }

        public static string GetLikeSqlWhere(string jurisdiction, string tableName)
        {
            var sbSqlWhere = new StringBuilder();
            if (!string.IsNullOrEmpty(jurisdiction))
            {
                var jurisdictionCodes = JsonConvert.DeserializeObject<List<SimpleDepartmentDto>>(jurisdiction);
                if (jurisdictionCodes.Count > 0)
                {
                    sbSqlWhere.Append(" (");
                    for (int i = 0; i < jurisdictionCodes.Count; i++)
                    {
                        var item = jurisdictionCodes[i];
                        var code = Regex.Replace(item.RegionCode, @"[0]{3}$", "");

                        var fldName = "town";
                        if (code.Length == 3) // 市
                        {
                            fldName = "province";
                        }
                        else if (code.Length == 6) // 区
                        {
                            fldName = "xzqdm";
                        }
                        else if (code.Length == 9) // 街道
                        {
                            fldName = "towndm";
                        }
                        else if (code.Length == 12) // 村庄
                        {
                            fldName = "villagedm";
                        }

                        if (i > 0)
                        {
                            sbSqlWhere.Append($@" or ");
                        }

                        if (string.IsNullOrEmpty(tableName))
                        {
                            sbSqlWhere.Append($@"{fldName} = '{item.RegionCode}'");
                        }
                        else
                        {
                            sbSqlWhere.Append($@"{tableName}.{fldName} = '{item.RegionCode}'");
                        }
                    }

                    sbSqlWhere.Append(")");
                }
            }

            return sbSqlWhere.ToString();
        }

        public static string GetLikeSqlWhere_Yt(string jurisdiction, string tableName)
        {
            var sbSqlWhere = new StringBuilder();
            if (!string.IsNullOrEmpty(jurisdiction))
            {
                var jurisdictionCodes = JsonConvert.DeserializeObject<List<SimpleDepartmentDto>>(jurisdiction);
                if (jurisdictionCodes.Count > 0)
                {
                    sbSqlWhere.Append(" (");
                    for (int i = 0; i < jurisdictionCodes.Count; i++)
                    {
                        var item = jurisdictionCodes[i];
                        var code = Regex.Replace(item.RegionCode, @"[0]{3}$", "");

                        var fldName = "town";
                        if (code.Length == 3) // 市
                        {
                            fldName = "district_id";
                        }
                        else if (code.Length == 6) // 区
                        {
                            fldName = "district_id";
                        }
                        else if (code.Length == 9) // 街道
                        {
                            fldName = "street__id2021";
                        }
                        else if (code.Length == 12) // 村庄
                        {
                            fldName = "village";
                        }

                        if (i > 0)
                        {
                            sbSqlWhere.Append($@" or ");
                        }

                        if (string.IsNullOrEmpty(tableName))
                        {
                            sbSqlWhere.Append($@"{fldName} = '{item.RegionCode}'");
                        }
                        else
                        {
                            sbSqlWhere.Append($@"{tableName}.{fldName} = '{item.RegionCode}'");
                        }
                    }

                    sbSqlWhere.Append(")");
                }
            }

            return sbSqlWhere.ToString();
        }

        public static string GetLikeSqlWhere_yt(string jurisdiction, string tableName)
        {     
            var sbSqlWhere = new StringBuilder();
            if (!string.IsNullOrEmpty(jurisdiction))
            {
                var jurisdictionCodes = JsonConvert.DeserializeObject<List<SimpleDepartmentDto>>(jurisdiction);
                if (jurisdictionCodes.Count > 0)
                {
                    sbSqlWhere.Append(" (");
                    for (int i = 0; i < jurisdictionCodes.Count; i++)
                    {
                        var item = jurisdictionCodes[i];
                        var code = Regex.Replace(item.RegionCode, @"[0]{3}$", "");

                        var fldName = "district_id";
                        if (code.Length == 3) // 市
                        {
                            return "";                        }
                        else
                        if (code.Length == 6) // 区
                        {
                            fldName = "district_id";
                        }
                        else if (code.Length == 9) // 街道
                        {
                            fldName = "street_id2021";
                            item.RegionCode = Regex.Replace(item.RegionCode, @"[0]{3}$", "");
                        }
                        else 
                        //if (code.Length == 12) // 村庄
                        //{
                        //    fldName = "village";
                        //}

                        if (i > 0)
                        {
                            sbSqlWhere.Append($@" or ");
                        }

                        if (string.IsNullOrEmpty(tableName))
                        {
                            sbSqlWhere.Append($@"{fldName} = '{code}'");
                        }
                        else
                        {
                            sbSqlWhere.Append($@"{tableName}.{fldName} = '{item.RegionCode}'");
                        }
                    }

                    sbSqlWhere.Append(")");
                }
            }

            return sbSqlWhere.ToString();
        }
        public static string GetLikeSqlWhereVillage(string jurisdiction, string tableName)
        {
            var sbSqlWhere = new StringBuilder();
            if (!string.IsNullOrEmpty(jurisdiction))
            {
                var jurisdictionCodes = JsonConvert.DeserializeObject<List<SimpleDepartmentDto>>(jurisdiction);
                if (jurisdictionCodes.Count > 0)
                {
                    sbSqlWhere.Append(" (");
                    for (int i = 0; i < jurisdictionCodes.Count; i++)
                    {
                        var item = jurisdictionCodes[i];
                        var code = Regex.Replace(item.RegionCode, @"[0]{3}$", "");

                        if (i > 0)
                        {
                            sbSqlWhere.Append($@" or ");
                        }

                        if (string.IsNullOrEmpty(tableName))
                        {
                            sbSqlWhere.Append($@"village like '{code}%'");
                        }
                        else
                        {
                            sbSqlWhere.Append($@"{tableName}.village like '{code}%'");
                        }
                    }

                    sbSqlWhere.Append(")");
                }
            }

            return sbSqlWhere.ToString();
        }
    }
}
